package top.jlzhou.test_data.city.imp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import top.jlzhou.test_data.city.CityService;

public class CityServiceImp extends CityService {
	
	static{
		try{
			Class.forName("org.sqlite.JDBC");
		}catch(Exception ex){
			ex.printStackTrace();
			System.out.println("未加载sqlite驱动");
			System.exit(0);
		}
	}
	public Connection getConn()throws Exception{
		return DriverManager.getConnection("jdbc:sqlite::resource:top/jlzhou/test_data/city/imp/city.db");
	}

	@Override
	public String randomAddress()throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			conn = getConn();
			pstmt = conn.prepareStatement("select hat_province.province,hat_city.city,tb.area from (select * FROM hat_area order by RANDOM() limit 1)tb INNER JOIN hat_city on tb.father=hat_city.cityID INNER JOIN hat_province on hat_city.father=hat_province.provinceID");
			rs = pstmt.executeQuery();
			rs.next();
			return rs.getString(1)+rs.getString(2)+rs.getString(3);
		}finally{
			close(conn, pstmt, rs);
		}
	}
	
	public void close(Connection conn,PreparedStatement pstmt,ResultSet rs){
		try{rs.close();}catch(Exception ex){}
		try{pstmt.close();}catch(Exception ex){}
		try{conn.close();}catch(Exception ex){}
	}

	public static void main(String[] args)throws Exception {
		CityService service = CityService.getService();
		System.out.println(service.randomAddress());
	}
}
